﻿using System;
using System.Collections.Generic;
using System.Text;
using System.Data.SqlClient;
using System.Windows.Forms;
using System.Data;

namespace AdatbazisProjekt
{
	class DataAlkatreszek : DalGen 
	{
		// alkatreszekkel kapcsolatos adatbazisban kereso modosito metodusokat implementalo osztaly
		public DataAlkatreszek(ref bool Err)
        {
            if (!base.IsConnectCreated())
            {
                Err = !base.MakeConnection();
            }
        }

		public List<string> GetComponentName(string componentTableName, ref bool Err)
		{
			// visszateriti az alkatreszek neveit melyek egy adott tablaban csoporosulnak lasd pl Processzorok
			List<string> pro = new List<string>();
			try
			{
				string error = "";
				string cmd = "SELECT Nev FROM " + componentTableName;
				SqlDataReader reader = mExecuteReader(cmd, ref error, null);
				while (reader.Read())
				{
					pro.Add(reader["Nev"].ToString());
				}
				this.CloseConnection();
				return pro;
			}
			catch (Exception)
			{
				Err = true;
				return pro;
			}
		}

        public List<String> GetGyartok(ref bool err)
        {
			// egy listat terit vissza az adatbazisban talalhato gyartokkal kapcsolatban
            List<String> gyartok = new List<String>();
            try
            {
                err = false;
                OpenConnection();
                string error = "";
                SqlDataReader reader = mExecuteReader("SELECT GyartoNev FROM Gyartok", ref error,null);
                while (reader.Read())
                {
                    gyartok.Add(reader["GyartoNev"].ToString());
                }
                this.CloseConnection();
                return gyartok;
            }
            catch (Exception)
            {
                err = true;
                this.CloseConnection();
                return gyartok;
            }
        }

		public int GetNewComponentIndex()
		{
			//az adatbazis felepiteseben van egy Alkatreszek tabla mely egyszerubbe teszi a kapcsolatot a tablak kozt, ebben csak ID vannak
			// ha egy uj alkatreszt szurunk be ki kell talalnunk mi legyen az ID-je h ne utkozon massal es ezt teszi ez a metodus
			try
			{
				string error = "";
				SqlDataReader reader = mExecuteReader("Select MAX(AlkID) as Eredmeny FROM Alkatreszek", ref error,null);
				reader.Read();
				int re = Convert.ToInt32(reader["Eredmeny"].ToString()) + 1;
				this.CloseConnection();
				return re;
			}
			catch (Exception e)
			{
				this.CloseConnection();
				MessageBox.Show(e.Message.ToString());
				return -1;
			}
		}

		public int GetGyartoID(string gyNev)
		{
			// visszateriti egy adott gyartonevhez tartozo gyartoID-t
			try
			{
				string error = "";
				SqlDataReader reader = mExecuteReader("Select  GyartoID FROM [Gyartok] Where GyartoNev = '"+gyNev+"'", ref error,null);
				reader.Read();
				int re = Convert.ToInt32(reader["GyartoID"].ToString());
				this.CloseConnection();
				return re;
			}
			catch (Exception e)
			{
				this.CloseConnection();
				MessageBox.Show(e.Message.ToString());
				return -1;
			}
		}

		public bool ExistProperties(string mertekegyseg, string nev, int ertek)
		{
			//Visszateriti hogy letezik-e mar egy adott tulajdonsag az adatbazisban
			bool re;
			try
			{
				string error = "";
				string cmd = "SELECT * FROM Tulajdonsagok Where Mertekegyseg = '"+mertekegyseg+"' AND Nev = '"+nev+"' AND Ertek = "+ertek.ToString()+" ";
				SqlDataReader reader = mExecuteReader(cmd, ref error,null);
				if (reader.Read()) re = true;
				else re = false;
				this.CloseConnection();
				return re;
			}
			catch (Exception)
			{
				this.CloseConnection();
				return false;
			}
		}

		private string TulajdKeres(string mertekEgyseg, string nev, string ertek)
		{
			// megvizsgalja h letezik-e egy adott tulajdonsag az TUlajdonsagok tablaban es ha nem akkor lethozza a kivat sort es visszateriti az ID-jet
			string id = "error";
            try
			{
				string error = "";
				bool err =  false;
        

				string cmd = "SELECT TulajdonsagID FROM Tulajdonsagok WHERE Mertekegyseg = '"+mertekEgyseg+"' AND Nev = '"+nev+"' AND Ertek = "+ertek+";";
				SqlDataReader reader = mExecuteReader(cmd, ref error,null);
				if (reader.Read())
				{
					id = reader["TulajdonsagID"].ToString();
				}
				this.CloseConnection();
				if (id == "error")
				{
                    
					this.MyExecuteNonQuery("INSERT INTO Tulajdonsagok VALUES ('"+mertekEgyseg+"','"+nev+"',"+ertek+")", ref err,null);
					cmd = "SELECT TulajdonsagID FROM Tulajdonsagok WHERE Mertekegyseg = '" + mertekEgyseg + "' AND Nev = '" + nev + "' AND Ertek = " + ertek + ";";
					reader = mExecuteReader(cmd, ref error,null);
					if (reader.Read())
					{
						id = reader["TulajdonsagID"].ToString();
					}
                    
					this.CloseConnection();
				}
				return id;
			}
			catch
			{
				return "error";
			}
		}

		public void UjProcesszor(string nev, int magSzam, int teljesitmeny, string szalSzam, string Gyarto, ref bool error)
		{
			// Uj processzor beszurasat vegzi az adatbazisba
            SqlTransaction tran = null;
            try
            {
                
                int index = this.GetNewComponentIndex();
                int gyID = this.GetGyartoID(Gyarto);
                string tulajdId = TulajdKeres("Db", "MagSzam", magSzam.ToString());
                string tulajdId2 = TulajdKeres("GHz", "Teljesitmeny", teljesitmeny.ToString());
                string tulajdId3 = TulajdKeres("Db", "ThreadSzam", szalSzam.ToString());
                CloseConnection();

                // PESZIMISTA TRANZAKCIO KEZELES
                OpenConnection();

                tran = mySqlConn.BeginTransaction(IsolationLevel.Serializable);

                
                this.MyExecuteNonQuery("INSERT INTO Alkatreszek VALUES (" + index.ToString() + ");", ref error,tran);
                this.MyExecuteNonQuery("INSERT INTO Processzorok VALUES (" + index.ToString() + ",'" + nev + "', " + gyID.ToString() + ");", ref error, tran);
				
				if (tulajdId != "error")
				{
                    this.MyExecuteNonQuery("INSERT INTO AlkTulajd VALUES (" + index.ToString() + "," + tulajdId + ");", ref error, tran);
				}
				else
				{
					error = true;
				}
				
				if (tulajdId != "error")
				{
                    this.MyExecuteNonQuery("INSERT INTO AlkTulajd VALUES (" + index.ToString() + "," + tulajdId2 + ");", ref error, tran);
				}
				else
				{
					error = true;
				}
				
				if (tulajdId != "error")
				{
                    this.MyExecuteNonQuery("INSERT INTO AlkTulajd VALUES (" + index.ToString() + "," + tulajdId3 + ");", ref error, tran);
				}
				else
				{
					error = true;
				}
                // COMMIT ########## 
                tran.Commit();
                tran.Dispose();
                //############
                CloseConnection();
            }
            catch (Exception)
            {
                // ROLLBACK ###############
                tran.Rollback();
                tran.Dispose();
                //######################
                error = true;
            }
       }


        public void UjMerevlemez(string nev, string kapacitas, string Gyarto, ref bool error)
        {
			// uj merevlemez beszurasat vegzi az adatbazisba
            SqlTransaction tran = null;
            try
            {
                int index = this.GetNewComponentIndex();
                int gyID = this.GetGyartoID(Gyarto);
                string tulajdId = TulajdKeres("DB", "Kapacitas", kapacitas.ToString());
                CloseConnection();

                OpenConnection();

                tran = mySqlConn.BeginTransaction(IsolationLevel.Serializable);

                this.MyExecuteNonQuery("INSERT INTO Alkatreszek VALUES (" + index.ToString() + ");", ref error, tran);
                this.MyExecuteNonQuery("INSERT INTO Merevlemezek VALUES (" + index.ToString() + ",'" + nev + "', " + gyID.ToString() + ");", ref error, tran);
				
				if (tulajdId != "error")
				{
					this.MyExecuteNonQuery("INSERT INTO AlkTulajd VALUES (" + index.ToString() + "," + tulajdId + ");", ref error, tran);
				}
				else
				{
					error = true;
				}
                tran.Commit();
                tran.Dispose();
	       }
            catch (Exception)
            {
                if (tran != null)
                {
                    tran.Rollback();
                    tran.Dispose();
                }
                error = true;
            }
        }

        public void UjKepernyo(string nev, string atmero, string Gyarto, ref bool error)
        {
			// Uj kepernyo beszurasat vegzi az adatbazisba
            SqlTransaction tran = null;
            try
            {
                int index = this.GetNewComponentIndex();
                int gyID = this.GetGyartoID(Gyarto);
                string tulajdId = TulajdKeres("Inch", "Atmero", atmero.ToString());
                CloseConnection();

                OpenConnection();
                tran = mySqlConn.BeginTransaction(IsolationLevel.Serializable);


                this.MyExecuteNonQuery("INSERT INTO Alkatreszek VALUES (" + index.ToString() + ");", ref error, tran);
                this.MyExecuteNonQuery("INSERT INTO Kepernyok VALUES (" + index.ToString() + ",'" + nev + "', " + gyID.ToString() + ");", ref error, tran);
				
				if (tulajdId != "error")
				{
					this.MyExecuteNonQuery("INSERT INTO AlkTulajd VALUES (" + index.ToString() + "," + tulajdId + ");", ref error, tran);
				}
				else
				{
					error = true;
				}
                tran.Commit();
                tran.Dispose();
            }
            catch (Exception)
            {
                if (tran != null)
                {
                    tran.Rollback();
                    tran.Dispose();
                }
                error = true;
            }
        }

        public void UjVideokartya(string nev, string atmero, string gyarto, ref bool error)
        {
			// Uj videokartya beszurasat  vezgi az adatbazisba
            try
            {
                error = false;
                List<String> paraNev = new List<string>();
                List<String> paraErt = new List<string>();

                paraNev.Add("@nev");
                paraNev.Add("@teljesitmeny");
                paraNev.Add("@gyarto");

                paraErt.Add(nev);
                paraErt.Add(atmero);
                paraErt.Add(gyarto);

                MyExecuteNonQueryWithParameters("EXEC UjVideokartya @nev, @teljesitmeny, @gyarto", paraNev, paraErt,null);
            }
            catch (Exception)
            {
                error = true;
            }
        }

        public void UjRAM(string nev, string meret, string tipus, string gyarto, ref bool error)
        {
			// Uj ram a beszurasat vegzi az adatbazisba
            try
            {
                error = false;
                List<String> paraNev = new List<string>();
                List<String> paraErt = new List<string>();

                paraNev.Add("@nev");
                paraNev.Add("@meret");
                paraNev.Add("@tipus");
                paraNev.Add("@gyarto");

                paraErt.Add(nev);
                paraErt.Add(meret);
                paraErt.Add(tipus);
                paraErt.Add(gyarto);

                MyExecuteNonQueryWithParameters("EXEC UjRAM @nev, @meret, @tipus, @gyarto", paraNev, paraErt,null);
            }
            catch (Exception)
            {
                error = true;
            }
        }

		public string GetComponentProperties(string component, string properties, string name)
		{
			// Visszateriti hogy ez adott komponens adott tulajdonsaganak mennyi az erteke
			string re = null;
			try
			{
				string error = "";
                string cmd = "Select Tulajdonsagok.Ertek From Tulajdonsagok, "+component+", Alkatreszek, AlkTulajd Where "+component+".AlkatreszID  = Alkatreszek.AlkID AND Alkatreszek.AlkID = AlkTulajd.AlkatreszID AND Tulajdonsagok.TulajdonsagID = AlkTulajd.TulajdonsagID AND "+component+".Nev ='"+name+"' AND Tulajdonsagok.Nev = '"+properties+"'";
				SqlDataReader reader = mExecuteReader(cmd, ref error, null);
                if (reader.Read())
                {
                    re = reader["Ertek"].ToString();
                }
				this.CloseConnection();
				return re;
			}
			catch (Exception e)
			{
				this.CloseConnection();
				MessageBox.Show(e.Message.ToString());
				return "Err";
			}
		}

		public string GetComponentID(string componentType, string componentName)
		{
			// visszateriti a egy alaktreszhez tartozo alkatresz ID-t
			string re;
			try
			{
				string error = "";
				SqlDataReader reader = mExecuteReader("Select AlkatreszID From " + componentType + " Where Nev= '"+componentName+"'", ref error, null);
				reader.Read();
				re = reader["AlkatreszID"].ToString();
				this.CloseConnection();
				return re;
			}
			catch (Exception e)
			{
				this.CloseConnection();
				MessageBox.Show(e.Message.ToString());
				return "Err";
			}
		}

		public List<string> GetTermekIDs(string ID)
		{
			// visszatertiti azokat a termeketet melyek egy adott alkatresszel vanna szerelve
			List<string> re = new List<string>();
			try
			{
				string error = "";
				SqlDataReader reader = mExecuteReader("Select TermekID From Felepites Where AlkatreszID =" + ID, ref error,null);
				while( reader.Read() ){
					re.Add( reader["TermekID"].ToString());
				}
				this.CloseConnection();
				return re;
			}
			catch (Exception e)
			{
				this.CloseConnection();
				MessageBox.Show(e.Message.ToString());
				return null;
			}
		}

		public void DeleteComponent(string componentType, string componentName, ref bool error)
		{
			// Ez a metodus altalanosan vegzi a parameterkent kapott alkatreszek es a hozzatartozo mas adatok torleset az adatbazisbol
            
            // PEszimista tranzakcio
            string iD = GetComponentID(componentType, componentName);
            List<string> termekIDs = GetTermekIDs(iD);
            CloseConnection();
            SqlTransaction tran = null;
            try
            {
                OpenConnection();
                tran = mySqlConn.BeginTransaction(IsolationLevel.Serializable);
            
                string cmd1 = "DELETE FROM AlkTulajd WHERE AlkatreszID =" + iD;
                string cmd2 = "DELETE FROM " + componentType + " WHERE AlkatreszID =" + iD;
                string cmd3 = "DELETE FROM Felepites WHERE AlkatreszID =" + iD;
                string cmd4 = "DELETE FROM Alkatreszek WHERE AlkID =" + iD;

                this.MyExecuteNonQuery(cmd1, ref error, tran);
                this.MyExecuteNonQuery(cmd2, ref error, tran);
                this.MyExecuteNonQuery(cmd3, ref error, tran);
                for (int i = 0; i < termekIDs.Count; i++)
                {
                    this.MyExecuteNonQuery("DELETE FROM Felepites WHERE TermekID =" + termekIDs[i], ref error, tran);
                }
                for (int i = 0; i < termekIDs.Count; i++)
                {
                    this.MyExecuteNonQuery("DELETE FROM Vasarlas WHERE TermekID =" + termekIDs[i], ref error, tran);
                }
                for (int i = 0; i < termekIDs.Count; i++)
                {
                    this.MyExecuteNonQuery("DELETE FROM Raktar WHERE TermekID =" + termekIDs[i], ref error, tran);
                }
                for (int i = 0; i < termekIDs.Count; i++)
                {
                    this.MyExecuteNonQuery("DELETE FROM Termekek WHERE TermekID =" + termekIDs[i], ref error, tran);
                }
                this.MyExecuteNonQuery(cmd4, ref error, tran);
                //COMMIT
                tran.Commit();
                tran.Dispose();
            }
            catch(Exception e)
            {
                if (tran != null)
                {
                    tran.Rollback();
                    tran.Dispose();
                }
                MessageBox.Show(e.Message);
            }
		}


        public void TorlesTrigerrel(string componentType, string componentName, ref bool error)
        {
			// A metodus azokra az alketreszekre mukodik melyek tablaihoz van delete triger irva
            try
            {
                string iD = GetComponentID(componentType, componentName);
                MyExecuteNonQuery("DELETE FROM " + componentType + " WHERE AlkatreszID =" + iD, ref error, null);
            }
            catch (Exception)
            {
                error = true;
            }
        }

        private Boolean optimistic(string componentType, string componentName, string prop1Name, string prop2Name, string prop3Name,string m1, string m2, string m3, string r1 ,string  r2,string r3)
        {
            string iD = GetComponentID(componentType, componentName);
            string err = "";
			 
            SqlDataReader reader = mExecuteReader("SELECT * FROM Alktulajd WHERE AlkatreszID = "+iD+" AND TulajdonsagID = (SELECT TulajdonsagID FROM Tulajdonsagok WHERE Nev = '"+prop1Name+"' AND Mertekegyseg = '"+m1+"' AND Ertek = "+r1+")", ref err, null);
            if(reader.Read()){
                CloseConnection();
                 if(prop2Name!=""){
                     SqlDataReader reader2 = mExecuteReader("SELECT * FROM Alktulajd WHERE AlkatreszID = "+iD+" AND TulajdonsagID = (SELECT TulajdonsagID FROM Tulajdonsagok WHERE Nev = '"+prop2Name+"' AND Mertekegyseg = '"+m2+"' AND Ertek = "+r2+")", ref err, null);
                     if(reader2.Read()){
                         CloseConnection();
                         if(prop3Name!=""){
                             SqlDataReader reader3 = mExecuteReader("SELECT * FROM Alktulajd WHERE AlkatreszID = "+iD+" AND TulajdonsagID = (SELECT TulajdonsagID FROM Tulajdonsagok WHERE Nev = '"+prop3Name+"' AND Mertekegyseg = '"+m3+"' AND Ertek = "+r3+")", ref err, null);         
                             if(reader3.Read()){
                                CloseConnection();
                                return true;
                             }
                             else{
                                 CloseConnection();
                                return false;
                             }
                        }
                        else{
                            CloseConnection();
                            return false;
                        }
                     }
                     else{
                         CloseConnection();
                        return false;
                     }
                 }
                 else{
                     CloseConnection();
                     return true;
                 }
            }
            else{
                CloseConnection();
                return false;
            }
            CloseConnection();
            return false;


        }


		public void AlkatreszFrissites(string componentType, string componentName, string prop1Name, string prop2Name, string prop3Name, string prop1, string prop2, string prop3, string m1, string m2, string m3, string r1 ,string  r2,string r3,  string productor,ref bool error)
		{
			// Az alkatreszek frisiteseit vegzo metodus
            // optimista tranzakcio
            try
            {
                
                if (optimistic(componentType, componentName, prop1Name, prop2Name, prop3Name, m1, m2, m3, r1, r2, r3))
                {
                    string iD = GetComponentID(componentType, componentName);
                    string tulajdId = "";

                    tulajdId = TulajdKeres(m1, prop1Name, prop1);

                    if (tulajdId != "error")
                    {
                        this.MyExecuteNonQuery("UPDATE AlkTulajd SET TulajdonsagID = " + tulajdId + " WHERE AlkatreszID = " + iD + " AND TulajdonsagID = (SELECT TulajdonsagID FROM Tulajdonsagok WHERE Nev = '" + prop1Name + "' AND Mertekegyseg = '" + m1 + "' AND Ertek = " + r1 + ")", ref error, null);
                    }
                    else
                    {
                        error = true;
                    }
                    if (prop2Name != "") // mivel altalanosan hasznaljuk a metodus, vannak olyan alkatreszek amelyeknek csak egy tulajdonsaga van igy azoknak a tobbu tullajdonsaga ""
                    {
                        tulajdId = TulajdKeres(m2, prop2Name, prop2);
                        if (tulajdId != "error")
                        {
                            this.MyExecuteNonQuery("UPDATE AlkTulajd SET TulajdonsagID = " + tulajdId + " WHERE AlkatreszID = " + iD + " AND TulajdonsagID = (SELECT TulajdonsagID FROM Tulajdonsagok WHERE Nev = '" + prop2Name + "' AND Mertekegyseg = '" + m2 + "' AND Ertek = " + r2 + ")", ref error, null);
                        }
                        else
                        {
                            error = true;
                        }

                    }
                    if (prop3Name != "")
                    {
                        tulajdId = TulajdKeres(m3, prop3Name, prop3);
                        if (tulajdId != "error")
                        {
                            this.MyExecuteNonQuery("UPDATE AlkTulajd SET TulajdonsagID = " + tulajdId + " WHERE AlkatreszID = " + iD + " AND TulajdonsagID = (SELECT TulajdonsagID FROM Tulajdonsagok WHERE Nev = '" + prop3Name + "' AND Mertekegyseg = '" + m3 + "' AND Ertek = " + r3 + ")", ref error, null);
                        }
                        else
                        {
                            error = true;
                        }

                    }
                }
            }
            catch(Exception e){
                MessageBox.Show(e.Message);
            }
		}

	}
}
